SQL formatting issue

Question

Post reply

SQL formatting issue

thinkastrategy

SSC Veteran

Points: 284
More actions
September 29, 2014 at 3:47 pm

#297044

This is the file name:
aSNAP_Data_20140929_P-2014-09-29_07.01.34.dat
I need to give this name to the SSIS package in such way that it automatically pucks up the current date's file and processes it.
I started it in the below fashion:
"\aSNAP_Data_"+cast(year(getdate()) as nvarchar)+......
Could anyone please finish the above? I am bit confused with the date functions. example - I am not sure how to get the date value as '09' instead of '9'.
Any help is highly appreciated. Thank you.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Something like this?

"aSNAP_Data_20140929_P-" + (DT_STR, 4, 1252) YEAR( GETDATE()) + "-" + RIGHT( "0" + (DT_STR, 2, 1252) MONTH( GETDATE()), 2) + "-" + RIGHT( "0" + (DT_STR, 2, 1252) DAY( GETDATE()), 2) + "_07.01.34.dat"

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

thinkastrategy SSC Veteran Points: 284 More actions · Answer 2

did you understand my question? How can you hard code the date and time values?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

SELECT FORMAT(getdate(),'aSNAP_Da\ta_yyyyMMdd_P-yyyy-MM-dd_hh.mm.ss.dat')

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I didn't realized that you had the date twice with different format and one had time. Here's a different option:

"aSNAP_Data_" + REPLACE( SUBSTRING( ((DT_WSTR, 29) GETDATE()), 1, 10), "-", "") + "_P-" + REPLACE( REPLACE( SUBSTRING( ((DT_WSTR, 29) GETDATE()), 1, 19), " ", "_"), ":", ".") + ".dat"

Are you working on T-SQL or SSIS?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2