Archive

Posts Tagged ‘time’

SQL Server – Add Date/Time to output file of BCP / SQLCMD (2)

April 12, 2013 2 comments

Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when xp_cmdshell is not enabled.

You can also run BCP/SQLCMD command on command prompt (cmd.exe) and append date/time to output file name. You can use below commands:

 

SQLCMD command to add date/time to output filename:

SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt

BCP command to add date/time to output filename:

bcp "EXEC ExportData" queryout MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt -S (local) -T -d SqlAndMe -c

 

You can use ECHO command to verify the file name:

ECHO MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt

 

How this works:

On command prompt %date% return current date in short format. The ":~6,4" part is like a SUBSTRING function which returns 4 characters starting from position 6, which returns year. Similarly, we are retrieving month, day, hour, minutes using same function and appending all of this together to generate the file name in format "MyFile_YYYYMMDD_HHMM.txt"

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Denali CTP3 – DATEFROMPARTS() and TIMEFROMPARTS()

July 18, 2011 Leave a comment

These are new functions in SQL Server "Denali". DATEFROMPARTS() returns a DATE value with specified day, month and year.

It takes three arguments:

DATEFROMPARTS (@Year, @Month, @Day)

If the arguments are invalid, it raises an error.

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT DATEFROMPARTS (2011, 07, 18) AS 'Today'

Result Set:

Today

———-

2011-07-18

 

(1 row(s) affected)

 

Similarly, we can use DATETIMEFROMPARTS() to generate a DATETIME value.

It takes the form:

DATETIMEFROMPARTS (@Year, @Month, @Day, @Hour, @Minute, @Seconds, @Milliseconds)

If the arguments are invalid, an error is raised.

SELECT DATETIMEFROMPARTS (2011, 07, 18, 21, 01, 20, 700) AS 'Today'

Result Set:

Today

———————–

2011-07-18 21:01:20.700

 

(1 row(s) affected)

 

There is a function for TIME also, TIMEFROMPARTS(). It takes below arguments:

TIMEFROMPARTS (@Hour, @Minute, @Seconds, @Fractions, @Precision)

SELECT TIMEFROMPARTS ( 21, 01, 20, 070, 3) AS 'Now'

Result Set:

Now

—————-

21:01:20.070

 

(1 row(s) affected)

Also, there are similar functions for DATETIME2, DATETIMEOFFSET and SMALLDATETIME.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Categories: SQLServer, SQLServer 2012